package dao;

import database.AccesoDB;
import entity.Empleado;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class EmpleadoDao implements Icrud<Empleado> {

    @Override
    public void grabar(Empleado o) throws Exception {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void editar(Empleado o) throws Exception {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void eliminar(Empleado o) throws Exception {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<Empleado> listar() throws Exception {
        Connection cn = null;
        List<Empleado> lista = new ArrayList<Empleado>();
        try {
            cn = AccesoDB.getConnection();
            String query = "select employee_id,first_name,last_name,department_id,salary from employees";
            Statement stm = cn.createStatement();
            ResultSet rset = stm.executeQuery(query);
            while (rset.next()) {
                Empleado o = new Empleado();
                o.setIdempleado(rset.getInt("employee_id"));
                o.setNombre(rset.getString("first_name"));
                o.setApellido(rset.getString("last_name"));
                o.setIddepartamento(rset.getInt("department_id"));
                o.setSueldo(rset.getDouble("salary"));
                lista.add(o);
            }
            rset.close();
            stm.close();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return lista;
    }

    @Override
    public List<Empleado> listacriterio(String nom) throws Exception {
        Connection cn = null;
        List<Empleado> lista = new ArrayList<Empleado>();
        try {
            cn = AccesoDB.getConnection();
            String query = "select employee_id,first_name,last_name,department_id,salary from employees where first_name like ?";
            PreparedStatement stm = cn.prepareStatement(query);
            stm.setString(1, nom + '%');
            ResultSet rset = stm.executeQuery();
            while (rset.next()) {
                Empleado o = new Empleado();
                o.setIdempleado(rset.getInt("employee_id"));
                o.setNombre(rset.getString("first_name"));
                o.setApellido(rset.getString("last_name"));
                o.setIddepartamento(rset.getInt("department_id"));
                o.setSueldo(rset.getDouble("salary"));
                lista.add(o);
            }
            rset.close();
            stm.close();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }

        return lista;
    }

    public List<Empleado> listarDepartamento(int depa) throws Exception {
        Connection cn = null;
        List<Empleado> lista = new ArrayList<Empleado>();
        try {
            cn = AccesoDB.getConnection();
            String query = "select employee_id,first_name,last_name,department_id,salary from employees where department_id=?";
            PreparedStatement stm = cn.prepareStatement(query);
            stm.setInt(1, depa);
            ResultSet rset = stm.executeQuery();
            while (rset.next()) {
                Empleado o = new Empleado();
                o.setIdempleado(rset.getInt("employee_id"));
                o.setNombre(rset.getString("first_name"));
                o.setApellido(rset.getString("last_name"));
                o.setIddepartamento(rset.getInt("department_id"));
                o.setSueldo(rset.getDouble("salary"));
                lista.add(o);
            }
            rset.close();
            stm.close();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return lista;
    }
    
    public Empleado dameUnEmpleado(int codemp) throws Exception {
        Connection cn = null;
        Empleado o = new Empleado();
        try {
            cn = AccesoDB.getConnection();
            String query = "select employee_id,first_name,last_name,department_id,salary from employees where employee_id=?";
            PreparedStatement stm = cn.prepareStatement(query);
            stm.setInt(1, codemp);
            ResultSet rset = stm.executeQuery();
            if (rset.next()) {
                o.setIdempleado(rset.getInt("employee_id"));
                o.setNombre(rset.getString("first_name"));
                o.setApellido(rset.getString("last_name"));
                o.setIddepartamento(rset.getInt("department_id"));
                o.setSueldo(rset.getDouble("salary"));
            } else {
                throw new Exception("Empleado no existe...");
            }
            rset.close();
            stm.close();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return o;
    }
}
